/* This file aims to create ID information to link the HIMSS database and AHA annual survey.  */
clear all
set more off
cd "" //targed directory


********** Extract hospital info in HIMSS data
clear
forvalues i=6/9 {
clear
quietly odbc list
quietly odbc query "200`i'"
quietly odbc desc "HAEntity"
odbc load, table("HAEntity")
rename MedicareNumber mcrnum
destring mcrnum,replace force
keep if HAEntityTypeId==1|HAEntityTypeId==6|HAEntityTypeId==8
keep HAEntityId UniqueId mcrnum Name Address1 Zip
tempfile haeid
save `haeid'


clear
quietly odbc list
quietly odbc query "200`i'"
quietly odbc desc "HAEntityApplication"
odbc load, table("HAEntityApplication")
merge m:1 HAEntityId using `haeid'
keep if _merge==3
drop _merge
keep UniqueId HAEntityId Name mcrnum Address1 Zip ApplicationId CategoryId Status SoftwareVendorId ContractYear ImplementedYear 
keep if CategoryId==6  
keep if ApplicationId==31  
tempfile himss`i'
save `himss`i''



********** Extract hospital info from AHA
clear 
use "./rawdata/aha_extract200`i'.dta",clear
*use ./cont_3rdyr/aha_extract200`i'.dta
gen year=200`i'
keep id mcrnum year mname mlocaddr mloczip bdtot
destring mcrnum,force replace

****** Export hospitals W/O mcrnum
export excel using ./prepare/mcrnum`i'_miss.xlsx if mcrnum==.,firstrow(var) replace
drop if mcrnum==.

bys mcrnum:egen maxbd=max(bdtot)
bys mcrnum:gen num=_N
drop if num>1&bdtot<maxbd
drop num maxbd
duplicates drop mcrnum,force  
tempfile aha`i'
save `aha`i''



********** Combine HIMSS and AHA
clear
use `himss`i'',clear
merge m:1 mcrnum using `aha`i''


****** Export hospitals only in HIMSS (_merge==1)
export excel id mcrnum year mname mlocaddr mloczip bdtot UniqueId HAEntityId Name Address1 Zip using "./prepare/mcrnum`i'_inHimss.xlsx" if _merge==1,firstrow(var) replace
drop if _merge==1


****** Export hospitals only in AHA (_merge==2)
export excel id mcrnum year mname mlocaddr mloczip bdtot UniqueId HAEntityId Name Address1 Zip using "./prepare/mcrnum`i'_inAHA.xlsx" if _merge==2,firstrow(var) replace
drop if _merge==2


****** Save the merged ones
keep id mcrnum year mname mlocaddr mloczip bdtot UniqueId HAEntityId Name Address1 Zip
save  "./prepare/mcrnum`i'_matched",replace
}



****** Note that the final output for this part is "id_matched.dta," after merging with hospitals (that cannot be matched using Medicare provider number) using geographic information, names, etc. (Some has to be done manually.)





























